package com.xdjmall.dao.impl;

import java.time.LocalDate;
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.junit.jupiter.api.Test;

import com.xdjmall.dao.UserDao;
import com.xdjmall.entity.Goods;
import com.xdjmall.entity.User;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class UserDaoImpl implements UserDao {
	
	@Override
	public Integer insert(User t) throws SQLException {
		// TODO 文琪
		QueryCondition qc = new QueryCondition();
		qc.put("loginName", t.getLoginName());
		Integer row2 = selectTotalUsers(qc);
		System.out.println(row2);
		
		
		if( row2 == 0) {
			String sql = "insert into user(nick_name,login_name,password_md5,introduce_sign,address) values(?,?,?,?,?)";
			try (Connection c = DBUtils.getConnection(); 
				PreparedStatement ps = c.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);) {
				ps.setString(1, t.getNickName());
				ps.setString(2, t.getLoginName());
				ps.setString(3, t.getPasswordMd5());
				ps.setString(4, t.getIntroduceSign());
				ps.setString(5, t.getAddress());
				int row = ps.executeUpdate();
				//得到返回的生成
				ResultSet keySet = ps.getGeneratedKeys();
				if (keySet.next()) {
					t.setUserId(keySet.getInt(1));
				}
				return row;
			}
		}
		return null;
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// TODO 文琪
		String sql = "update user set is_deleted=1 where user_id =? and is_deleted=0";
		try (Connection c = DBUtils.getConnection(); 
			PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, id);
			int row = ps.executeUpdate();
			return row;
		}
	}

	@Override
	public Integer update(User t) throws SQLException {
		// TODO 随鑫
		String sql = "update user set ";
		if (t.getNickName() != null) {
			sql += "nick_name=?,";
		}
		if (t.getLoginName() != null) {
			sql += "login_name=?,";
		}
		if (t.getPasswordMd5() != null) {
			sql += "password_md5=?,";
		}
		if (t.getIntroduceSign() != null) {
			sql += "introduce_sign=?,";
		}
		if (t.getAddress() != null) {
			sql += "address=?,";
		}
		sql = sql.substring(0,sql.length()-1);
		sql += " where user_id=? and is_deleted=0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (t.getNickName() != null) {
				ps.setString(num++, t.getNickName());
			}
			if (t.getLoginName() != null) {
				ps.setString(num++, t.getLoginName());
			}
			if (t.getPasswordMd5() != null) {
				ps.setString(num++, t.getPasswordMd5());
			}
			if (t.getIntroduceSign() != null) {
				ps.setString(num++, t.getIntroduceSign());
			}
			if (t.getAddress() != null) {
				ps.setString(num++, t.getAddress());
			}
			ps.setInt(num, t.getUserId());
			int row = ps.executeUpdate();
			return row;
		}
	}

	@Override
	public User selectById(Integer id) throws SQLException {
		// TODO 随鑫
		String sql = "select nick_name,login_name,password_md5,introduce_sign,address,locked_flag,create_time"
				+ " from user where user_id=? and is_deleted=0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				User u = new User();
				u.setUserId(id);
				u.setNickName(rs.getString(1));
				u.setLoginName(rs.getString(2));
				u.setPasswordMd5(rs.getString(3));
				u.setIntroduceSign(rs.getString(4));
				u.setAddress(rs.getString(5));
				u.setLockedFlag(rs.getBoolean(6));
				u.setCreateTime(rs.getDate(7).toLocalDate());
				return u;
			}
		}
		return null;
	}

	@Override
	public Integer selectCount() throws SQLException {
		// TODO 王子龙
		String sql = " select count(*) from user where is_deleted=0";
		Connection c = DBUtils.getConnection();
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}else {
			return null;
		}
	}

	@Override
	public List<User> selectByPage(QueryCondition condition) throws SQLException {
		// TODO 孙庆洋
		List<User> users = new ArrayList<>();
		String sql = "select user_id,nick_name,login_name,password_md5,introduce_sign,address,locked_flag,create_time from user where is_deleted=0 ";
		if (condition.getPage() != null && condition.getPageSize() != null) {
			sql += " limit ?,? ";
		}
		try(Connection c = DBUtils.getConnection();
			PreparedStatement ps = c.prepareStatement(sql);){
			Integer page = condition.getPage();
			Integer pageSize = condition.getPageSize();
			if (page != null && pageSize != null) {
				ps.setInt(1, (page - 1) * pageSize);
				ps.setInt(2, pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setUserId(rs.getInt(1));
				u.setNickName(rs.getString(2));
				u.setLoginName(rs.getString(3));
				u.setPasswordMd5(rs.getString(4));
				u.setIntroduceSign(rs.getString(5));
				u.setAddress(rs.getString(6));
				u.setLockedFlag(rs.getBoolean(7));
				u.setCreateTime(rs.getDate(8).toLocalDate());
				users.add(u);
			}
			return users.isEmpty() ? Collections.emptyList() : users;
		}
		
	}

	@Override
	public User selectByLoginName(String loginName) throws SQLException {
		// TODO 王子龙
		String sql = " select user_id,nick_name,login_name,password_md5,introduce_sign,address,locked_flag,create_time "
				+ " from user where login_name = ? and is_deleted = 0";
		Connection c = DBUtils.getConnection();
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, loginName);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			User u = new User();
			u.setUserId(rs.getInt(1));
			u.setNickName(rs.getString(2));
			u.setLoginName(rs.getString(3));
			u.setPasswordMd5(rs.getString(4));
			u.setIntroduceSign(rs.getString(5));
			u.setAddress(rs.getString(6));
			u.setLockedFlag(rs.getBoolean(7));
			u.setCreateTime(rs.getDate(8).toLocalDate());
			return u;
		}
		return null;
	}

	
	@Test
	public void test() throws SQLException {
		QueryCondition qc = new QueryCondition();
		Object put = qc.put("loginName", "123");
		Integer row = selectTotalUsers(qc);
		System.out.println(row);
	}
	
	// condition中没有个key是loginName，那么查询所有行的行数，否则查询符合登录名的行数
	@Override
	public Integer selectTotalUsers(QueryCondition condition) throws SQLException {
		// TODO 孙庆洋
		String sql = "select count(*) from user where is_deleted=0 ";
		if (condition!= null && condition.get("loginName") != null && condition.get("loginName") != "") {
			sql += " and login_name = ?";
		}
		try (Connection c = DBUtils.getConnection(); 
			PreparedStatement ps = c.prepareStatement(sql);) {
			if (condition!= null && condition.get("loginName") != null && condition.get("loginName") != "") {
				ps.setObject(1, condition.get("loginName"));
			}
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		}
		return null;
	}

	@Override
	public Integer lockUserBatch(Integer[] ids, Boolean status) throws SQLException {
		// TODO 孙庆洋
		String sql = "update user set locked_flag = ? where is_deleted=0 and user_id " + SQLHandler.joinIn(ids);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setBoolean(1, status);
			int row = ps.executeUpdate();
			return row;
		}
	}
}
